﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using Database.Core.Exceptions;

namespace Database.Sql
{
  /// <summary>
  /// MS SQL stored procedure.
  /// </summary>
  public static class StoredProc
  {
    private const int COMMAND_TIMEOUT = 180;

    //TODO: Simplify user defined type parameter declaration

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static DataSet GetDataSet(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataSet(procedure, (Parameters) null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    public static DataSet GetDataSet(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataSet(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    public static DataSet GetDataSet(string procedure, Parameters parameters, string connectionName)
    {
      string connectionString;

      if(connectionName.Contains(";"))
      {
        connectionString = connectionName;
      }
      else
      {
        ValidateConnection(connectionName);

        connectionString = Environment.Connections[connectionName];
      }

      return FillDataSet(procedure, parameters, connectionString);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    public static Tuple<DataSet, dynamic> GetDataSetWithOutput(string procedure, Parameters parameters)
    {
      if(Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataSetWithOutput(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    public static Tuple<DataSet, dynamic> GetDataSetWithOutput(string procedure, Parameters parameters, string connectionName)
    {
      string connectionString;

      if(connectionName.Contains(";"))
      {
        connectionString = connectionName;
      }
      else
      {
        ValidateConnection(connectionName);

        connectionString = Environment.Connections[connectionName];
      }

      return FillDataSetWithOutput(procedure, parameters, connectionString);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static DataTable GetDataTable(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataTable(procedure, null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    public static DataTable GetDataTable(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataTable(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    public static DataTable GetDataTable(string procedure, Parameters parameters, string connectionName)
    {
      return GetDataTable(procedure, parameters, connectionName, 0);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    /// <param name="tableIndex">Index within tables collection to return</param>
    public static DataTable GetDataTable(string procedure, Parameters parameters, string connectionName, int tableIndex)
    {
      string connectionString;

      if(connectionName.Contains(";"))
      {
        connectionString = connectionName;
      }
      else
      {
        ValidateConnection(connectionName);

        connectionString = Environment.Connections[connectionName];
      }

      return FillDataSet(procedure, parameters, connectionString).Tables[tableIndex];
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    public static Tuple<DataTable, dynamic> GetDataTableWithOutput(string procedure, Parameters parameters)
    {
      if(Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataTableWithOutput(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    public static Tuple<DataTable, dynamic> GetDataTableWithOutput(string procedure, Parameters parameters, string connectionName)
    {
      return GetDataTableWithOutput(procedure, parameters, connectionName, 0);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    /// <param name="tableIndex">Index within tables collection to return</param>
    public static Tuple<DataTable, dynamic> GetDataTableWithOutput(string procedure, Parameters parameters, string connectionName, int tableIndex)
    {
      string connectionString;

      if(connectionName.Contains(";"))
      {
        connectionString = connectionName;
      }
      else
      {
        ValidateConnection(connectionName);

        connectionString = Environment.Connections[connectionName];
      }

      var dataSetTuple = FillDataSetWithOutput(procedure, parameters, connectionString);

      return new Tuple<DataTable, dynamic>(dataSetTuple.Item1.Tables[tableIndex], dataSetTuple.Item2);
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static SqlDataReader GetDataReader(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataReader(procedure, null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    public static SqlDataReader GetDataReader(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return GetDataReader(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    public static SqlDataReader GetDataReader(string procedure, Parameters parameters, string connectionName)
    {
      ValidateConnection(connectionName);

      var sqlCommand = new SqlCommand(procedure, new SqlConnection(Environment.Connections[connectionName]))
      {
        CommandType = CommandType.StoredProcedure,
        CommandTimeout = 180
      };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
        {
          sqlCommand.Parameters.Add(sqlParameter);
        }
      }

      if (sqlCommand.Connection.State != ConnectionState.Open)
      {
        sqlCommand.Connection.Open();
      }

      return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
    }

    /// <summary>
    /// Executes the provided procedure.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return Execute(procedure, null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return Execute(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string procedure, Parameters parameters, string connectionName)
    {
      ValidateConnection(connectionName);

      var sqlParameter1 = new SqlParameter("@RETURN_VALUE", SqlDbType.Int)
      {
        Direction = ParameterDirection.ReturnValue
      };

      using (var sqlConnection = new SqlConnection(Environment.Connections[connectionName]))
      {
        var sqlCommand = new SqlCommand(procedure, sqlConnection)
        {
          CommandType = CommandType.StoredProcedure,
          CommandTimeout = COMMAND_TIMEOUT
        };

        if (parameters != null)
        {
          foreach (var sqlParameter in parameters)
          {
            sqlCommand.Parameters.Add(sqlParameter);
          }
        }

        sqlCommand.Parameters.Add(sqlParameter1);

        if (sqlCommand.Connection.State != ConnectionState.Open)
          sqlCommand.Connection.Open();

        sqlCommand.ExecuteNonQuery();
      }

      return Convert.ToInt32(sqlParameter1.Value);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return ExecuteScalar(procedure, null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return ExecuteScalar(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string procedure, Parameters parameters, string connectionName)
    {
      ValidateConnection(connectionName);

      object obj;

      using (var sqlConnection = new SqlConnection(Environment.Connections[connectionName]))
      {
        var sqlCommand = new SqlCommand(procedure, sqlConnection)
        {
          CommandType = CommandType.StoredProcedure,
          CommandTimeout = COMMAND_TIMEOUT
        };

        if (parameters != null)
        {
          foreach (var sqlParameter in parameters)
          {
            sqlCommand.Parameters.Add(sqlParameter);
          }
        }

        if (sqlCommand.Connection.State != ConnectionState.Open)
        {
          sqlCommand.Connection.Open();
        }

        obj = sqlCommand.ExecuteScalar();
      }

      return obj;
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <typeparam name="T">Return Type</typeparam>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    /// <exception cref="DefaultConnectionNotDefined">The Default Connection has not been defined. Please define prior to use.</exception>
    public static T ExecuteScalar<T>(string procedure)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return ExecuteScalar<T>(procedure, (Parameters) null, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <typeparam name="T">Return Type</typeparam>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <returns>
    /// Result as T
    /// </returns>
    /// <exception cref="DefaultConnectionNotDefined">The Default Connection has not been defined. Please define prior to use.</exception>
    public static T ExecuteScalar<T>(string procedure, Parameters parameters)
    {
      if (Environment.Connections.DefaultConnection == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      }

      return ExecuteScalar<T>(procedure, parameters, Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// </summary>
    /// <typeparam name="T">Return Type</typeparam>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <param name="parameters">Parameter list</param>
    /// <param name="connectionName">Connection key/name</param>
    /// <returns>
    /// Result as T
    /// </returns>
    public static T ExecuteScalar<T>(string procedure, Parameters parameters, string connectionName)
    {
      ValidateConnection(connectionName);

      T value;

      using (var sqlConnection = new SqlConnection(Environment.Connections[connectionName]))
      {
        var sqlCommand = new SqlCommand(procedure, sqlConnection)
        {
          CommandType = CommandType.StoredProcedure,
          CommandTimeout = COMMAND_TIMEOUT
        };

        if (parameters != null)
        {
          foreach (var sqlParameter in parameters)
          {
            sqlCommand.Parameters.Add(sqlParameter);
          }
        }

        if (sqlCommand.Connection.State != ConnectionState.Open)
        {
          sqlCommand.Connection.Open();
        }

        //TODO: make sure the scalar value can be converted to T
        value = (T)sqlCommand.ExecuteScalar();
      }

      return value;
    }

    /// <summary>
    /// Validates that given connections keys/names are valid
    /// </summary>
    /// <param name="connection"/>
    private static void ValidateConnection(string connection)
    {
      if (connection == string.Empty)
        throw new UnknownConnection("Connection key/name cannot be empty.");

      if (Environment.Connections[connection] == string.Empty)
        throw new UnknownConnection("Connection key/name '" + connection + "' refers to an unknown connection.");
    }

    /// <summary>
    /// Returns a filled dataset
    /// </summary>
    /// <param name="procedure">Name of stored procedure to execute</param>
    /// <param name="parameters">Parameters</param>
    /// <param name="connectionString">Connection string</param>
    /// <returns/>
    private static DataSet FillDataSet(string procedure, IEnumerable<SqlParameter> parameters, string connectionString)
    {
      if(connectionString == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The connection string has not been defined!");
      }

      var dataSet = new DataSet();

      using(var sqlConnection = new SqlConnection(connectionString))
      {
        var selectCommand = new SqlCommand(procedure, sqlConnection)
        {
          CommandType = CommandType.StoredProcedure,
          CommandTimeout = COMMAND_TIMEOUT
        };

        try
        {
          if(parameters != null)
          {

            foreach(var sqlParameter in parameters)
            {
              selectCommand.Parameters.Add(sqlParameter);
            }
          }

          var sqlDataAdapter = new SqlDataAdapter(selectCommand);

          sqlDataAdapter.Fill(dataSet);
        }
        finally
        {
          selectCommand.Parameters.Clear();

          if(selectCommand.Connection.State == ConnectionState.Open)
          {
            selectCommand.Connection.Close();
          }
        }
      }

      return dataSet;
    }

    /// <summary>
    /// Returns a filled dataset
    /// </summary>
    /// <param name="procedure">Name of stored procedure to execute</param>
    /// <param name="parameters">Parameters</param>
    /// <param name="connectionString">Connection string</param>
    /// <returns></returns>
    private static Tuple<DataSet, dynamic> FillDataSetWithOutput(string procedure, IEnumerable<SqlParameter> parameters, string connectionString)
    {
      if(connectionString == string.Empty)
      {
        throw new DefaultConnectionNotDefined("The connection string has not been defined!");
      }

      var dataSet = new DataSet();
      var output = new ExpandoObject() as IDictionary<string, object>;

      using(var sqlConnection = new SqlConnection(connectionString))
      {
        var selectCommand = new SqlCommand(procedure, sqlConnection)
        {
          CommandType = CommandType.StoredProcedure,
          CommandTimeout = COMMAND_TIMEOUT
        };

        try
        {
          var outputParams = new List<SqlParameter>();

          if(parameters != null)
          {
            foreach(var sqlParameter in parameters)
            {
              if(sqlParameter.Direction != ParameterDirection.Input)
              {
                outputParams.Add(sqlParameter);
              }

              selectCommand.Parameters.Add(sqlParameter);
            }
          }

          var sqlDataAdapter = new SqlDataAdapter(selectCommand);

          sqlDataAdapter.Fill(dataSet);

          if(outputParams.Any())
          {
            foreach(var odbcParameter in outputParams)
            {
              output.Add(odbcParameter.ParameterName, odbcParameter.Value);
            }
          }
        }
        finally
        {
          selectCommand.Parameters.Clear();

          if(selectCommand.Connection.State == ConnectionState.Open)
          {
            selectCommand.Connection.Close();
          }
        }
      }

      return new Tuple<DataSet, dynamic>(dataSet, output);
    }
  }
}